USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[ResultJumpOfHeight_Juni]    Script Date: 06/09/2014 13:32:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultJumpOfHeight_Juni]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultJumpOfHeight_Juni]
GO

CREATE PROCEDURE [dbo].[ResultJumpOfHeight_Juni]
	@TAIKAINO int,
	@KYOGINO int,
	@ROUNDNO int,
	@SUBNO int,
	@KUMINO int,
	@EXE2FLG int,
	@SORTFLG int,
	@SIGIFINDFIG int
AS
DECLARE @SQL AS VARCHAR(MAX)

	SET @SQL = '
		SELECT
			SK.TAIKAINO,
			SK.KYOGINO,
			SK.KYOGISYANO,
			LA.KUMINO,
			RE.KIROKU,
			LA.LANE, 
			RE.Sort,
			dbo.Jyuni(CASE WHEN RE.JYUNI IS NULL THEN '''' ELSE RE.JYUNI END ,SK.OPENFLG) AS JYUNI, 
			LA.SYUKKETU,
			RE.EXTEND2,
			(SELECT COUNT(*)
				FROM TBL_SANKAKYOGI AS SK,'
	IF @SIGIFINDFIG = 1
		BEGIN
			SET @SQL = @SQL + ' TBL_RESULT AS RE,'
		END

	SET @SQL = @SQL + '						
					 TBL_LANE AS LA 
				WHERE  LA.TAIKAINO = '+ STR(@TAIKAINO) +' 
						AND LA.KYOGINO = '+ STR(@KYOGINO) +' 
						AND LA.ROUNDNO = '+ STR(@ROUNDNO) +' 
						AND LA.SUBNO = '+ STR(@SUBNO) +' 
						AND LA.KUMINO = '+ STR(@KUMINO) +'
						AND LA.TAIKAINO = SK.TAIKAINO 
						AND LA.KYOGISYANO = SK.KYOGISYANO 
						AND LA.KYOGINO = SK.KYOGINO  '
						
	IF @SIGIFINDFIG = 1
		BEGIN
			SET @SQL = @SQL + '
						AND LA.TAIKAINO = RE.TAIKAINO 
						AND LA.KYOGINO = RE.KYOGINO 
						AND LA.SUBNO = RE.SUBNO 
						AND LA.ROUNDNO = RE.ROUNDNO 
						AND LA.KUMINO = RE.KUMINO 
						AND LA.LANE = RE.LANE 
						AND RE.EXTEND1 LIKE CASE WHEN '+ STR(@EXE2FLG) +' = 0 THEN ''%'' ELSE ''1'' END' 
		END

	SET @SQL = @SQL + '		   
			) AS MAXLANE
		FROM TBL_LANE AS LA 
			LEFT OUTER JOIN 
			(SELECT  DISTINCT 
				RE.LANE,
				RE.JYUNI,
				RE.JYUNI AS Sort,
				RE.KIROKU,
				RE.EXTEND2,
				RE.EXTEND1
			FROM TBL_LANE AS LA, 
				 TBL_RESULT AS RE
			WHERE LA.TAIKAINO = '+ STR(@TAIKAINO) +' 
				AND LA.KYOGINO = '+ STR(@KYOGINO) +' 
				AND LA.ROUNDNO = '+ STR(@ROUNDNO) +' 
				AND LA.SUBNO = '+ STR(@SUBNO) +' 
				AND LA.KUMINO = '+ STR(@KUMINO) +'
				AND LA.TAIKAINO = RE.TAIKAINO 
				AND LA.KYOGINO = RE.KYOGINO 
				AND LA.SUBNO = RE.SUBNO 
				AND LA.ROUNDNO = RE.ROUNDNO 
				AND LA.KUMINO = RE.KUMINO 
				AND LA.LANE = RE.LANE 
			)AS RE 
			ON LA.LANE = RE.LANE,
			   TBL_SANKAKYOGI AS SK 
		WHERE LA.TAIKAINO = '+ STR(@TAIKAINO) +' 
			AND LA.KYOGINO = '+ STR(@KYOGINO) +' 
			AND LA.ROUNDNO = '+ STR(@ROUNDNO) +' 
			AND LA.SUBNO = '+ STR(@SUBNO) +' 
			AND LA.KUMINO = '+ STR(@KUMINO) +'
			AND LA.TAIKAINO = SK.TAIKAINO 
			AND LA.KYOGISYANO = SK.KYOGISYANO 
			AND LA.KYOGINO = SK.KYOGINO '
	IF @SIGIFINDFIG = 1
		BEGIN
			SET @SQL = @SQL + ' AND RE.EXTEND1 LIKE CASE WHEN '+ STR(@EXE2FLG) +' = 0 THEN ''%'' ELSE ''1'' END'
		END

	SET @SQL = @SQL + ' ORDER BY '

	IF @SORTFLG = 1
		BEGIN
			SET @SQL = @SQL + ' 
				CASE
					WHEN '+ STR(@SORTFLG) +' =  1 THEN 
						CASE WHEN LA.SYUKKETU = 2 THEN 1 ELSE 0 END
				END ASC, 
				CASE
					WHEN '+ STR(@SORTFLG) +' =  1 THEN 
						CASE WHEN RE.Sort IS NULL OR RE.Sort = 0 THEN LA.LANE
							WHEN RE.Sort IS NULL OR RE.Sort = 0 THEN 1 
						END
				END ASC, 
				CASE
					WHEN '+ STR(@SORTFLG) +' =  1 THEN 
						RE.Sort
				END ASC,
			'

			END

	SET @SQL = @SQL + '	LA.LANE ASC'

	--PRINT @SQL
	EXECUTE(@SQL)

RETURN 0
GO

